﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using iTeamyou.LTS.Core.Models;
using iTeamyou.LTS.Core.Utils;
using iTeamyou.LTS.UI.Models;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.Streaming;
using NPOI.XSSF.UserModel;

namespace iTeamyou.LTS.UI.Utils
{
    public class NPOIHelper : IExcelHelper
    {
        public List<T> ExcelToList<T>(Stream stream, string fileName = "upload.xls", string sheetName = "") where T : new()
        {
            var sheetIndex = 0;

            IWorkbook workbook;
            string _ext = fileName.Substring(fileName.LastIndexOf("."), fileName.Length - fileName.LastIndexOf("."));
            if (_ext == ".xls")
            {
                workbook = new HSSFWorkbook(stream);
            }
            else
            {
                workbook = new XSSFWorkbook(stream);
            }

            if (!String.IsNullOrEmpty(sheetName))
                sheetIndex = workbook.GetSheetIndex(sheetName);

            if (sheetIndex == -1)
                return null;

            ISheet sheet = workbook.GetSheetAt(sheetIndex);
            IRow ITitleRow = sheet.GetRow(0);
            int totalColumn = ITitleRow.LastCellNum;
            int totalRow = sheet.LastRowNum;

            Dictionary<string, int> dic = new();
            Dictionary<int, string> dicBizDataType = new();
            var properties = typeof(T).GetProperties();
            for (int i = 0, len = properties.Length; i < len; i++)
            {
                object[] _attributes = properties[i].GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (_attributes.Length == 0)
                {
                    continue;
                }

                string _bizDataType = "";
                string _description = ((DescriptionAttribute)_attributes[0]).Description;
                object[] _attributesBizDataType = properties[i].GetCustomAttributes(typeof(BizDataType), false);

                if (_attributesBizDataType.Length > 0)
                    _bizDataType = ((BizDataType)_attributesBizDataType[0]).Description;

                if (!string.IsNullOrWhiteSpace(_description))
                {
                    dic.Add(_description, i);
                    dicBizDataType.Add(i, _bizDataType);
                }   
            }

            string _value = string.Empty;
            List<T> list = new();
            for (int i = 1; i <= totalRow; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                var obj = new T();
                for (int j = 0; j < totalColumn; j++)
                {
                    int index;
                    if (dic.TryGetValue(ITitleRow.GetCell(j).ToString(), out index) && row.GetCell(j) != null)
                    {
                        string _type = (properties[index].PropertyType).FullName;

                        if (_type == "System.String")
                        {
                            _value = row.GetCell(j).ToString().Replace("`", "").Replace("'", "");
                            properties[index].SetValue(obj, _value, null);
                        }
                        else if (_type == "System.DateTime")
                        {
                            _value = row.GetCell(j).ToString().Replace("`", "").Replace("'", "");
                            if (String.IsNullOrEmpty(_value))
                            {
                                DateTime pdt = DateTime.MinValue;
                                properties[index].SetValue(obj, pdt, null);
                            }
                            else
                            {
                                try
                                {
                                    DateTime pdt = DateTime.Parse(Convert.ToDateTime(_value, CultureInfo.CurrentCulture).ToString("yyyy-MM-dd"));
                                    properties[index].SetValue(obj, pdt, null);
                                }
                                catch
                                {
                                    if (Regex.IsMatch(_value, "^.*-.*月-.*$"))
                                    {
                                        DateTime pdt = DateTime.Parse(Convert.ToDateTime(_value.Replace("-", "/").Replace("月", ""), new CultureInfo("fr-FR")).ToString("yyyy-MM-dd"));
                                        properties[index].SetValue(obj, pdt, null);
                                    }
                                    else
                                    {
                                        DateTime pdt = DateTime.MaxValue.AddYears(-1);
                                        properties[index].SetValue(obj, pdt, null);
                                    }
                                }
                            }
                        }
                        else if (_type == "System.Boolean")
                        {
                            bool pb = Convert.ToBoolean(_value);
                            properties[index].SetValue(obj, pb, null);
                        }
                        else if (_type == "System.Int16")
                        {
                            short pi16 = Convert.ToInt16(Convert.ToDecimal(_value));
                            properties[index].SetValue(obj, pi16, null);
                        }
                        else if (_type.Contains("System.Int32"))
                        {
                            _value = row.GetCell(j).ToString().Replace("`", "").Replace("'", "");
                            if (!string.IsNullOrEmpty(_value))
                            {
                                int pi32 = 0;
                                try
                                {
                                    pi32 = Convert.ToInt32(Convert.ToDecimal(_value));
                                }
                                catch
                                {
                                    
                                }
                                
                                properties[index].SetValue(obj, pi32, null);
                            }
                        }
                        else if (_type == "System.Int64")
                        {
                            long pi64 = Convert.ToInt64(Convert.ToDecimal(_value));
                            properties[index].SetValue(obj, pi64, null);
                        }
                        else if (_type == "System.Byte")
                        {
                            byte pb = Convert.ToByte(_value);
                            properties[index].SetValue(obj, pb, null);
                        }
                        else if (_type.Contains("System.Decimal"))
                        {
                            decimal pd;
                            switch (dicBizDataType[index].ToUpper())
                            {
                                //case "PERCENTAGE":
                                //    pd = Convert.ToDecimal(row.GetCell(j).NumericCellValue / 100);
                                //    break;
                                default:
                                    try
                                    {
                                        pd = Convert.ToDecimal(row.GetCell(j).NumericCellValue);
                                    }
                                    catch
                                    {
                                        pd = 0;
                                    }
                                    
                                    break;
                            }
                            if(row.GetCell(j).CellType != CellType.Blank)
                                properties[index].SetValue(obj, pd, null);
                        }
                        //else if (_type == "Skyring.CheckingTools.Models.TradeStatus")
                        //{
                        //    switch (_value.ToUpper())
                        //    {
                        //        case "SUCCESS":
                        //            properties[index].SetValue(obj, TradeStatus.SUCCESS, null);
                        //            break;
                        //        case "支付成功":
                        //            properties[index].SetValue(obj, TradeStatus.SUCCESS, null);
                        //            break;
                        //        case "REFUND":
                        //            properties[index].SetValue(obj, TradeStatus.REFUND, null);
                        //            break;
                        //        default:
                        //            properties[index].SetValue(obj, TradeStatus.UNKNOWN, null);
                        //            break;
                        //    }
                        //}
                        else
                        {
                            properties[index].SetValue(obj, null, null);
                        }
                    }
                }
                list.Add(obj);
            }
            return list;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">转换Excel源数据</param>
        /// <param name="sheetName">工作表名</param>
        /// <param name="filePath">输出文件全路径</param>
        /// <param name="fileExt">输出文件扩展名，若filePath不为空，则优先使用filePath的文件扩展名</param>
        /// <returns></returns>
        public string ExportToExcel<T>(List<T> data, string sheetName = null, string filePath = null, string fileExt = ".xlsx") where T : new()
        {
            if (data.Count == 0)
            {
                filePath = Path.Combine(Directory.GetCurrentDirectory(), "Import_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
            }

            if (string.IsNullOrEmpty(filePath))
            {
                filePath = Path.Combine(Directory.GetCurrentDirectory(), "Export_" + DateTime.Now.ToString("yyyyMMddHHmmss") + fileExt);
            }
            else
            {
                fileExt = filePath.Substring(filePath.LastIndexOf("."), filePath.Length - filePath.LastIndexOf("."));
            }    
                


            IWorkbook workbook;
            if (File.Exists(filePath))
            {
                using var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                if(fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook(fs);
                }
                else
                {
                    workbook = new HSSFWorkbook(fs);
                }
            }
            else
            {
                //var file = File.Create(filePath);
                //file.Close();
                //file.Dispose();
                if (fileExt == ".xlsx")
                {
                    workbook = new XSSFWorkbook();
                    XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
                }
                else
                {
                    workbook = new HSSFWorkbook();
                    HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
                }
            }
            
            ISheet sheet;
            if (string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.CreateSheet();
            } 
            else
            {
                sheet = workbook.CreateSheet(sheetName);
            }

            IRow headerRow = sheet.CreateRow(0);

            Dictionary<int, string> dicDescription = new();
            Dictionary<int, string> dicProperty = new();
            Dictionary<int, string> dicType = new();
            Dictionary<int, string> dicBizType = new();
            Dictionary<int, string> dicBizDataType = new();
            Dictionary<int, int> dicBizCellWidth = new();
            var properties = typeof(T).GetProperties();
            for (int i = 0, len = properties.Length; i < len; i++)
            {
                object[] _attributes = properties[i].GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (_attributes.Length == 0)
                    continue;
                object[] _attributesBizType = properties[i].GetCustomAttributes(typeof(BizType), false);
                object[] _attributesBizDataType = properties[i].GetCustomAttributes(typeof(BizDataType), false);
                object[] _attributesBizCellWidth = properties[i].GetCustomAttributes(typeof(BizCellWidth), false);
                string _description = ((DescriptionAttribute)_attributes[0]).Description;
                string _property = properties[i].Name;

                string _bizType = "";
                string _bizDataType = "";
                int _bizCellWidth = 0;

                if (_attributesBizType.Length > 0)
                    _bizType = ((BizType)_attributesBizType[0]).Description;    
                if (_attributesBizDataType.Length > 0)
                    _bizDataType = ((BizDataType)_attributesBizDataType[0]).Description;
                if (_attributesBizCellWidth.Length > 0)
                    _bizCellWidth = ((BizCellWidth)_attributesBizCellWidth[0]).Width;

                if (!string.IsNullOrWhiteSpace(_description))
                {
                    dicDescription.Add(i, _description);
                    dicProperty.Add(i, _property);
                    dicType.Add(i, properties[i].PropertyType.FullName);
                    dicBizType.Add(i, _bizType);
                    dicBizDataType.Add(i, _bizDataType);
                    dicBizCellWidth.Add(i, _bizCellWidth);
                }
            }

            ICellStyle cellStyleGreen = workbook.CreateCellStyle();
            ICellStyle cellStylePURPLE = workbook.CreateCellStyle();
            ICellStyle cellStyleYELLOW = workbook.CreateCellStyle();
            ICellStyle cellStyleDefault = workbook.CreateCellStyle();
            ICellStyle cellStylePercentage = workbook.CreateCellStyle();
            ICellStyle cellStyleGeneral = workbook.CreateCellStyle();

            IFont font = workbook.CreateFont();
            font.Color = HSSFColor.Blue.Index;
            ICellStyle cellStyleURL = workbook.CreateCellStyle();
            cellStyleURL.SetFont(font);

            for (int i = 0; i < dicDescription.Count; i++)
            {
                ICell cell = headerRow.CreateCell(i);
                cell.SetCellValue(dicDescription[i]);

                if (dicBizType[i].Length != 0)
                {
                    switch (dicBizType[i].ToUpper())
                    {
                        case "GREEN":
                            cellStyleGreen.FillForegroundColor = HSSFColor.Lime.Index;
                            cellStyleGreen.FillPattern = FillPattern.SolidForeground;
                            cellStyleGreen.WrapText = true;
                            cell.CellStyle = cellStyleGreen;
                            break;
                        case "PURPLE":
                            cellStylePURPLE.FillForegroundColor = HSSFColor.LightCornflowerBlue.Index;
                            cellStylePURPLE.FillPattern = FillPattern.SolidForeground;
                            cellStylePURPLE.WrapText = true;
                            cell.CellStyle = cellStylePURPLE;
                            break;
                        case "YELLOW":
                            cellStyleYELLOW.FillForegroundColor = HSSFColor.Gold.Index;
                            cellStyleYELLOW.FillPattern = FillPattern.SolidForeground;
                            cellStyleYELLOW.WrapText = true;
                            cell.CellStyle = cellStyleYELLOW;
                            break;
                        default:
                            //cellStyleDefault.FillForegroundColor = HSSFColor.Grey50Percent.Index;
                            //cellStyleDefault.FillPattern = FillPattern.SolidForeground;
                            cellStyleDefault.WrapText = true;
                            cell.CellStyle = cellStyleDefault;
                            break;
                    }
                }
                else
                {
                    cell.CellStyle = cellStyleDefault;
                }

                if (dicBizCellWidth[i] != 0)
                    sheet.SetColumnWidth(i, dicBizCellWidth[i]);
                else
                    sheet.SetColumnWidth(i, Encoding.UTF8.GetBytes(dicDescription[i].ToString()).Length * 300);
            }

            headerRow.HeightInPoints = 40;

            if (data.Count > 0)
            {
                Type t = typeof(T);
                int rowIndex = 1;
                foreach (T item in data)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    for (int n = 0; n < dicDescription.Count; n++)
                    {
                        object pValue = t.GetProperty(dicProperty[n]).GetValue(item, null);
                        var cell = dataRow.CreateCell(n);
                        if (dicType[n].ToLower().Contains("system.decimal") || dicType[n].ToLower().Contains("system.int"))
                        {
                            //dataRow.CreateCell(n).SetCellType(CellType.Numeric);
                            //dataRow.GetCell(n).SetCellValue(Double.Parse((pValue ?? "").ToString()));
                            if (pValue != null)
                            {
                                cell.SetCellValue(Double.Parse((pValue ?? "").ToString()));
                                cell.SetCellType(CellType.Numeric);
                            }
                        }
                        else if (dicType[n].ToLower().Contains("productlicensetype") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((ProductLicenseType)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("supplierlicensetype") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((SupplierLicenseType)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("manufacturerlicensetype") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((ManufacturerLicenseType)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("batchlicensetype") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((BatchLicenseType)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("status") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((Status)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("region") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((Region)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("producttype") && pValue != null)
                        {
                            cell.SetCellValue((EnumUtil.GetDescriptionByEnum((ProductType)pValue) ?? "").ToString());
                        }
                        else if (dicType[n].ToLower().Contains("datetime") && pValue != null)
                        {
                            cell.SetCellValue(((DateTime)pValue).ToString("yyyy/MM/dd"));
                        }
                        else
                            cell.SetCellValue((pValue ?? "").ToString());

                        switch(dicBizDataType[n].ToUpper())
                        {
                            case "PERCENTAGE":
                                IDataFormat dataFormat = workbook.CreateDataFormat();
                                cellStylePercentage.DataFormat = dataFormat.GetFormat("0.0%");
                                cell.CellStyle = cellStylePercentage;
                                break;
                            case "URL":
                                cell.CellStyle = cellStyleURL;
                                var link = (pValue ?? "").ToString();
                                if (!string.IsNullOrEmpty(link))
                                {
                                    cell.SetCellValue("点击下载");
                                    if (fileExt == ".xlsx")
                                    {
                                        var XSSFLink = new XSSFHyperlink(HyperlinkType.Url)
                                        {
                                            Address = (pValue ?? "").ToString()
                                        };
                                        cell.Hyperlink = XSSFLink;
                                    }
                                    else
                                    {
                                        var HSSFLink = new HSSFHyperlink(HyperlinkType.Url)
                                        {
                                            Address = (pValue ?? "").ToString()
                                        };
                                        cell.Hyperlink = HSSFLink;
                                    }
                                }
                                else
                                    cell.SetCellValue("");
                                break;
                            case "LISTURL":
                                var links = (List<string>)pValue;
                                if (links != null && links.Count > 0)
                                {
                                    for (var i = 0; i < links.Count; i++)
                                    {
                                        if (i == 0)
                                        {
                                            cell.SetCellValue("点击下载");
                                            cell.CellStyle = cellStyleURL;
                                            if (fileExt == ".xlsx")
                                            {
                                                var XSSFLink = new XSSFHyperlink(HyperlinkType.Url)
                                                {
                                                    Address = (links[i] ?? "").ToString()
                                                };
                                                cell.Hyperlink = XSSFLink;
                                            }
                                            else
                                            {
                                                var HSSFLink = new HSSFHyperlink(HyperlinkType.Url)
                                                {
                                                    Address = (links[i] ?? "").ToString()
                                                };
                                                cell.Hyperlink = HSSFLink;
                                            }
                                        }
                                        else
                                        {
                                            var newCell = dataRow.CreateCell(n+i);
                                            newCell.SetCellValue("点击下载");
                                            newCell.CellStyle = cellStyleURL;
                                            if (fileExt == ".xlsx")
                                            {
                                                var XSSFLink = new XSSFHyperlink(HyperlinkType.Url)
                                                {
                                                    Address = (links[i] ?? "").ToString()
                                                };
                                                newCell.Hyperlink = XSSFLink;
                                            }
                                            else
                                            {
                                                var HSSFLink = new HSSFHyperlink(HyperlinkType.Url)
                                                {
                                                    Address = (links[i] ?? "").ToString()
                                                };
                                                newCell.Hyperlink = HSSFLink;
                                            }
                                        }
                                    }
                                }
                                else
                                    cell.SetCellValue("");
                                
                                break;
                            default:
                                cell.CellStyle = cellStyleGeneral;
                                break;
                        }
                    }
                    rowIndex++;
                }
            }

            try
            {
                FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
                workbook.Write(fs);
                workbook.Close();
                fs.Close();
                fs.Dispose();
            }
            catch
            {
                throw;
            }

            return filePath;
        }

        private static ICellStyle GetCellStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.FillPattern = FillPattern.SolidForeground;
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;

            return style;
        }
    }
}
